import sqlite3
from flask import Flask, g
app = Flask(__name__)
DATABASE = 'sql.db'

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)

    # 默认返回的行是tuple的，可改为dict格式 
    def make_dicts(cursor, row):
        return dict((cursor.description[idx][0], value) for idx, value in enumerate(row))
    db.row_factory = make_dicts
    # 或者， 返回Row对象
    # db.row_factory = sqlite3.Row
    return db


def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv

@app.teardown_appcontext
def close_db(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

# 初始化schema，建表
def init_db():
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()

@app.route('/')
def index():
    for user in query_db('select * from user'):
        print(user['username'] + str(user['id']))
    return 'ok'

@app.route('/one/<name>')
def one(name):
    user = query_db('select * from user where username=?', [name], one=True)
    if user is None:
        return "no user"
    else:
        return name + ', pwd is ' + user['password']

@app.route('/insert')
def insert():
    db = get_db()
    db.execute("INSERT INTO user(username, password) VALUES('admin', '123455')")
    db.commit()
    return "ok"
# 不是在一个请求中使用
# with app.app_context():
    # now you can use get_db()

if __name__ == "__main__":
    # init_db()
    app.run(debug=True)